package com.scienzeinternazionali.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.scienzeinternazionali.application.ConnectionManager;
import com.scienzeinternazionali.dto.Post;
import com.scienzeinternazionali.exception.DataAccessException;
import com.scienzeinternazionali.exception.DataSourceException;
import com.scienzeinternazionali.exception.PostException;
import com.scienzeinternazionali.util.LogFactory;

public class PostDAO {

		
		  private static Logger logger = LogFactory.getLogger(LogFactory.SCIENZEINTERNAZIONALI);

		  private Statement st = null;
		  private ResultSet rs = null;
		  private Connection conn = null;
		  private PreparedStatement pstm = null;

		  public void inserisciPost(Post post)throws DataAccessException, PostException{
			  logger.debug("inserisciPost IN");
			  String insert;
			  try {
				    conn = ConnectionManager.getConnection();
				    insert = "INSERT into post (data_inserimento,data_ultima_modifica,titolo,post,id_user, stato, link, top) values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,?,?,?,?,?,?)";
					logger.debug("insert :" + insert);
					pstm = conn.prepareStatement(insert);
					pstm.setString(1, post.getTitolo());
					pstm.setString(2, post.getPost());
					pstm.setString(3, post.getId_user());
					pstm.setInt(4, post.getStato());
					pstm.setString(5, post.getLink());
					pstm.setInt(6, post.getSticky());
					pstm.executeUpdate();
					pstm.close();
					}catch (SQLException e) {
						e.printStackTrace();
						logger.fatal(e.getMessage());
						throw new PostException("errore durante inserimento del post");
					} catch (Exception e) {
						e.printStackTrace();
						logger.fatal(e.getMessage());
						throw new DataAccessException("errore generale durante inserimento del post");
					} finally {
						ConnectionManager.closeAll(rs, pstm, conn);
						logger.debug("inserisciPost OUT");
					}
		  }

		  public void aggiornaPost(Post post)throws DataAccessException, PostException{
			  logger.debug("aggiornaPost IN");
			  String update;
			  try {
				conn = ConnectionManager.getConnection();
				update = "UPDATE post set data_ultima_modifica = CURRENT_TIMESTAMP, titolo = ?,post = ?,id_user = ?, stato = ?, link = ?, top=? where id_post = ?";
				System.out.println(update);
				System.out.println("aggiornaPost post.getId_user() "+post.getId_user());
				System.out.println("aggiornaPost post.getStato()"+post.getStato());
				System.out.println("aggiornaPost post.getId_post()"+post.getId_post());
				System.out.println("aggiornaPost post.getLink()"+post.getLink());
			    logger.debug("update :" + update);
			    pstm = conn.prepareStatement(update);
			    pstm.setString(1, post.getTitolo());
			    pstm.setString(2, post.getPost());
			    pstm.setString(3, post.getId_user());
			    pstm.setInt(4, post.getStato());
			    pstm.setString(5, post.getLink());
			    pstm.setInt(6, post.getSticky());
			    pstm.setInt(7, post.getId_post());
			    pstm.executeUpdate();
			    pstm.close();	
			}catch (SQLException e) {
				e.printStackTrace();
				logger.fatal(e.getMessage());
				throw new PostException("errore durante l'aggiornameno del post");
			} catch (Exception e) {
				e.printStackTrace();
				logger.fatal(e.getMessage());
				throw new DataAccessException("errore generale durante l'aggiornamento del post");
			} finally {
				ConnectionManager.closeAll(rs, pstm, conn);
				logger.debug("aggiornaPost OUT");
			}
		  }

			public Post getPost(Post post) throws DataAccessException, PostException{
				logger.debug("getPost IN");
				try {
			        String sql= "SELECT DATE_FORMAT(data_ultima_modifica,'%d-%m-%Y %H:%i:%s'), DATE_FORMAT(data_inserimento,'%d-%m-%Y %H:%i:%s'), titolo, post, nome, cognome, stato, link, top from post, login where login.id_user=post.id_user and id_post = "+post.getId_post();
			        logger.debug(sql);
			        conn = ConnectionManager.getConnection();
			        st = conn.createStatement();
			        rs = st.executeQuery(sql);
			        while (rs.next()) {
			        	post.setData_ultima_modifica(rs.getString(1));
			        	post.setData_inserimento(rs.getString(2));
			        	post.setTitolo(rs.getString(3));
			        	post.setPost(rs.getString(4));
			        	post.setUser(rs.getString(5)+" "+rs.getString(6));
			        	post.setStato(rs.getInt(7));
			        	post.setLink(rs.getString(8));
			        	post.setSticky(rs.getInt(9));
				    }
				}catch (DataSourceException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException(e.getMessage());
				} catch (SQLException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new PostException("errore generale durante il caricamento dei dati");
				} catch (Exception e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException("errore generale durante l'accesso al DB");
				} finally {
					ConnectionManager.closeAll(rs, pstm, conn);
					logger.debug("getPost OUT");
				}
				return post;
			}

			public List<Post> getAllPost() throws DataAccessException, PostException{
				logger.debug("getAllPost IN");
				List<Post> elencoPost = new ArrayList<Post>();
				try {
			        String sql= "SELECT data_ultima_modifica, data_inserimento, titolo, id_user, id_post, stato, link, top from post order by data_inserimento desc";
			        logger.debug(sql);
			        conn = ConnectionManager.getConnection();
			        st = conn.createStatement();
			        rs = st.executeQuery(sql);
			        while (rs.next()) {
			        	Post post = new Post();
			        	post.setData_ultima_modifica(rs.getString(1));
			        	post.setData_inserimento(rs.getString(2));
			        	post.setTitolo(rs.getString(3));
			        	post.setId_user(rs.getString(4));
			        	post.setId_post(rs.getInt(5));
			        	post.setStato(rs.getInt(6));
			        	post.setLink(rs.getString(7));
			        	post.setSticky(rs.getInt(8));
				        elencoPost.add(post);
				    }
				}catch (DataSourceException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException(e.getMessage());
				} catch (SQLException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new PostException("errore generale durante il caricamento della lista dei post");
				} catch (Exception e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException("errore generale durante l'accesso al DB");
				} finally {
					ConnectionManager.closeAll(rs, pstm, conn);
					logger.debug("getPost OUT");
				}
				return elencoPost;
			}
			public List<Post> getAllPostStato(int stato) throws DataAccessException, PostException{
				logger.debug("getAllPostStato IN");
				logger.debug("getAllPostStato si stanno cercando tutti i post in stato "+stato);
				List<Post> elencoPost = new ArrayList<Post>();
				try {
			        String sql= "SELECT DATE_FORMAT(data_ultima_modifica,'%d-%m-%Y %H:%i:%s'), DATE_FORMAT(data_inserimento,'%d-%m-%Y %H:%i:%s'), titolo, login.id_user, nome, cognome, id_post, post, link, top from post, login where login.id_user=post.id_user and stato = "+stato+" order by data_inserimento desc";
			        logger.debug(sql);
			        conn = ConnectionManager.getConnection();
			        st = conn.createStatement();
			        rs = st.executeQuery(sql);
			        while (rs.next()) {
			        	Post post = new Post();
			        	post.setData_ultima_modifica(rs.getString(1));
			        	post.setData_inserimento(rs.getString(2));
			        	post.setTitolo(rs.getString(3));
			        	post.setId_user(rs.getString(4));
			        	post.setUser(rs.getString(5)+" "+rs.getString(6));
			        	post.setId_post(rs.getInt(7));
			        	post.setPost(rs.getString(8));
			        	post.setLink(rs.getString(9));
			        	post.setSticky(rs.getInt(10));
                        elencoPost.add(post);
				    }
				}catch (DataSourceException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException(e.getMessage());
				} catch (SQLException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new PostException("errore generale durante il caricamento della lista dei post per stato "+stato);
				} catch (Exception e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException("errore generale durante l'accesso al DB");
				} finally {
					ConnectionManager.closeAll(rs, pstm, conn);
					logger.debug("getPost OUT");
				}
				return elencoPost;
			}
			public List<Post> getAllPostTopPublicati() throws DataAccessException, PostException{
				logger.debug("getAllPostTop IN");
				logger.debug("getAllPostTop si stanno cercando tutti i post in top");
				List<Post> elencoPost = new ArrayList<Post>();
				try {
			        String sql= "SELECT DATE_FORMAT(data_ultima_modifica,'%d-%m-%Y %H:%i:%s'), DATE_FORMAT(data_inserimento,'%d-%m-%Y %H:%i:%s'), titolo, login.id_user, nome, cognome, id_post, post, link, top from post, login where login.id_user=post.id_user and top = 1 and stato=1 order by data_inserimento desc";
			        logger.debug(sql);
			        conn = ConnectionManager.getConnection();
			        st = conn.createStatement();
			        rs = st.executeQuery(sql);
			        while (rs.next()) {
			        	Post post = new Post();
			        	post.setData_ultima_modifica(rs.getString(1));
			        	post.setData_inserimento(rs.getString(2));
			        	post.setTitolo(rs.getString(3));
			        	post.setId_user(rs.getString(4));
			        	post.setUser(rs.getString(5)+" "+rs.getString(6));
			        	post.setId_post(rs.getInt(7));
			        	post.setPost(rs.getString(8));
			        	post.setLink(rs.getString(9));
			        	post.setSticky(rs.getInt(10));
                        elencoPost.add(post);
				    }
				}catch (DataSourceException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException(e.getMessage());
				} catch (SQLException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new PostException("errore generale durante il caricamento della lista dei post per top");
				} catch (Exception e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException("errore generale durante l'accesso al DB");
				} finally {
					ConnectionManager.closeAll(rs, pstm, conn);
					logger.debug("getPost OUT");
				}
				return elencoPost;
			}
			public List<Post> getAllPostPublicati() throws DataAccessException, PostException{
				logger.debug("getAllPostTop IN");
				logger.debug("getAllPostTop si stanno cercando tutti i post in top");
				List<Post> elencoPost = new ArrayList<Post>();
				try {
			        String sql= "SELECT DATE_FORMAT(data_ultima_modifica,'%d-%m-%Y %H:%i:%s'), DATE_FORMAT(data_inserimento,'%d-%m-%Y %H:%i:%s'), titolo, login.id_user, nome, cognome, id_post, post, link, top from post, login where login.id_user=post.id_user and top = 0 and stato=1 order by data_inserimento desc";
			        logger.debug(sql);
			        conn = ConnectionManager.getConnection();
			        st = conn.createStatement();
			        rs = st.executeQuery(sql);
			        while (rs.next()) {
			        	Post post = new Post();
			        	post.setData_ultima_modifica(rs.getString(1));
			        	post.setData_inserimento(rs.getString(2));
			        	post.setTitolo(rs.getString(3));
			        	post.setId_user(rs.getString(4));
			        	post.setUser(rs.getString(5)+" "+rs.getString(6));
			        	post.setId_post(rs.getInt(7));
			        	post.setPost(rs.getString(8));
			        	post.setLink(rs.getString(9));
			        	post.setSticky(rs.getInt(10));
                        elencoPost.add(post);
				    }
				}catch (DataSourceException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException(e.getMessage());
				} catch (SQLException e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new PostException("errore generale durante il caricamento della lista dei post per top");
				} catch (Exception e) {
					logger.fatal(e.getMessage());
					e.printStackTrace();
					throw new DataAccessException("errore generale durante l'accesso al DB");
				} finally {
					ConnectionManager.closeAll(rs, pstm, conn);
					logger.debug("getPost OUT");
				}
				return elencoPost;
			}
			  public void eliminaPost(Post post)throws DataAccessException, PostException{
				  logger.debug("eliminaPost IN");
				  String delete;
				  try {
					  conn = ConnectionManager.getConnection();
					  delete = "delete from post where id_post = ?";
				logger.debug("delete :" + delete);
				pstm = conn.prepareStatement(delete);
				pstm.setInt(1, post.getId_post());
				pstm.executeUpdate();
				pstm.close();
				}catch (SQLException e) {
					e.printStackTrace();
					logger.fatal(e.getMessage());
					throw new PostException("errore durante eliminazione del post");
				} catch (Exception e) {
					e.printStackTrace();
					logger.fatal(e.getMessage());
					throw new DataAccessException("errore generale durante eliminazione del post");
				} finally {
					ConnectionManager.closeAll(rs, pstm, conn);
					logger.debug("eliminaPost OUT");
				}
			  }
			
	}
